<?php
namespace WDB\SQLDriver;
use WDB,
    WDB\Exception,
    WDB\Query\Query,
    WDB\Query\Insert,
    WDB\Query\Delete,
    WDB\Query\Select,
    WDB\Query\Update,
    WDB\Query\Element;

/**
 * Base iSQLDriver implementation for common issues.
 *
 * @author Richard Ejem <richard(at)ejem.cz>
 * @package WDB
 */
abstract class BaseSQL implements iSQLDriver
{
    public function query(Query $query)
    {
        if ($query instanceof Select)
        {
            $q = $this->select($query);
        }
        elseif ($query instanceof Delete)
        {
            $q = $this->delete($query);
        }
        elseif ($query instanceof Insert)
        {
            $q = $this->insert($query);
        }
        elseif ($query instanceof Update)
        {
            $q = $this->update($query);
        }
        else
        {
            throw new WDB\Exception\BadArgument("unknown select class ".get_class($query));
        }
        return $q;
    }

    protected $functionChangeNames = array('CURRENT_SCHEMA'=>'DATABASE');
    protected $functionPassthrough = array('COUNT','VALUES','MAX','MIN','SUM');

    /**
     *
     * @param string name
     * @param iElement[] args
     * @param bool native
     * @return string
     * @throws WDB\Exception\BadArgument
     */
    public function tosql_function($name, $args, $native = FALSE)
    {
        if (!$native)
        {
            //pass-through names
            if (in_array($name, $this->functionPassthrough))
            {
                //OK
            }
            elseif (isset($this->functionChangeNames[$name]))
            {
                $name = $this->functionChangeNames[$name];
            }
            else
            {
                throw new Exception\Unimplemented("function $name is not supported by current database driver: ".get_class($this));
            }
        }
        $out = $this->escape_identifier($name).'(';
        $first = TRUE;
        foreach ($args as $arg)
        {
            if (!$arg instanceof Element\iElement)
            {
                throw new WDB\Exception\BadArgument("every sql function argument must be iElement, ".gettype($arg).'('.(is_object($arg) ? get_class($arg) : (string)$arg).') given');
            }
            if ($first)
            {
                $first = FALSE;
            }
            else
            {
                $out .= ', ';
            }

            $out .= $arg->toSQL($this);
        }
        $out .= ')';
        return $out;
    }

    public function tosql_compare($expr1, $expr2, $operator)
    {
        if($expr2 instanceof Element\Datatype\TNull)
        {
            $operator = ($operator == '=') ? 'IS' : 'IS NOT';
            return '('.$expr1->toSQL($this).") $operator ".$expr2->toSQL($this);
        }
        elseif ($expr1 instanceof Element\Datatype\TNull)
        {
            $operator = ($operator == '=') ? 'IS' : 'IS NOT';
            return '('.$expr2->toSQL($this).") $operator ".$expr1->toSQL($this);
        }
        return '('.$expr1->toSQL($this).") $operator (".$expr2->toSQL($this).')';
    }
    public function tosql_logic($expressions, $operator)
    {
        $first_expr = array_shift($expressions);
        if (\strtoupper($operator) == 'NOT')
        {
            return 'NOT ('.$first_expr->toSQL($this).')';
        }
        else
        {
            $q = '('.$first_expr->toSQL($this).')';
            foreach ($expressions as $expr)
            {
                $q .= " $operator (".$expr->toSQL($this).')';
            }
            return $q;
        }
    }
    public function tosql_exprOp($expressions, $operator)
    {
        $first_expr = array_shift($expressions);
        $q = '('.$first_expr->toSQL($this).')';
        foreach ($expressions as $expr)
        {
            $q .= " $operator (".$expr->toSQL($this).')';
        }
        return $q;
    }

    public function tosql_column(Element\ColumnIdentifier $identifier)
    {
        $r = '';
        if ($identifier->schema)
        {
            $r .= $this->tosql_identifier($identifier->schema).'.';
        }
        if ($identifier->table)
        {
            $r .= $this->tosql_identifier($identifier->table).'.';
        }
        if ($identifier->column === Element\ColumnIdentifier::ALL_COLUMNS)
        {
            return $r.'*';
        }
        else
        {
            return $r.$this->tosql_identifier($identifier->column);
        }
    }

    public function tosql_identifier($identifier)
    {
        return $this->quote_identifier($this->escape_identifier($identifier));
    }

    public function tosql_null()
    {
        return 'NULL';
    }

    public function tosql_string($value)
    {
        if ($value === NULL) return 'NULL';
        return $this->quote_string($this->escape_string($value));
    }

    public function tosql_enum($value)
    {
        if ($value === NULL) return 'NULL';
        return $this->tosql_string($value);
    }

    public function tosql_set($value)
    {
        if ($value === NULL) return 'NULL';
        return $this->tosql_string(implode(',', $value));
        // in MySQL, no selected value is represented as an empty string.
        // we`ll see how this behaves in other databases.
        // and YES it is strange, because there can also be an element
        // of the set represented by an empty string and selecting it
        // is something different than selecting zero elements.
    }
    public function tosql_number($value)
    {
        if ($value === NULL) return 'NULL';
        return intval($value);
    }

    public function tosql_table(Element\TableIdentifier $table)
    {
        if ($table->schema !== NULL)
        {
            $tbl = $this->tosql_identifier($table->schema).'.';
        }
        else
        {
            $tbl = '';
        }
        $tbl .= $this->tosql_identifier($table->table);
        return $tbl;
    }

    public function tosql_tableJoin(Element\Join $join)
    {
        $ta = $join->getTableA()->toSQL($this);
        $tb = $join->getTableB()->toSQL($this);
        switch ($join->getType())
        {
            case Element\Join::INNER:
                $clause = 'JOIN';
                break;
            case Element\Join::LEFT:
                $clause = 'LEFT JOIN';
                break;
            case Element\Join::RIGHT:
                $clause = 'RIGHT JOIN';
                break;
            case Element\Join::OUTER:
                throw new \WDB\Exception\Unimplemented("This database does not support full outer join and wdb simulation is not implemented yet.");
        }
        if ($join->getCondition() === Element\Join::NATURAL)
        {
            $clause = "NATURAL $clause";
        }

        if ($join->getCondition() instanceof Element\iCondition)
        {
            $condition = ' ON '.$join->getCondition()->toSQL($this);
        }
        elseif ($join->getCondition() instanceof Element\JoinUsing)
        {
            $condition = ' USING ('.$join->getCondition()->toSQL($this).')';
        }
        else
        {
            $condition = '';
        }
        return sprintf('%s %s %s %s', $ta, $clause, $tb, $condition);
    }

    public function tosql_joinUsingClause(Element\JoinUsing $using)
    {
        $first = true;
        $result = '';
        foreach ($using as $u)
        {
            if ($first) $first = false;
            else $result .= ', ';
            $result .= $u->toSQL($this);
        }
        return $result;
    }

    public function queryString(\WDB\Query\Query $q)
    {
        if ($q instanceof Select)
        {
            $str = $this->tosql_select($q);
        }
        elseif ($q instanceof Delete)
        {
            $str = $this->tosql_delete($q);
        }
        elseif ($q instanceof Insert)
        {
            $str = $this->tosql_insert($q);
        }
        elseif ($q instanceof Update)
        {
            $str = $this->tosql_update($q);
        }
        else
        {
            throw new \WDB\Exception\BadArgument("unknown select class ".get_class($q));
        }
        return $str;
    }

    public function tosql_subselect(Select $select)
    {
        return '('.$this->tosql_select($select).')';
    }

    public function tosql_aliasedTableSource(Element\AliasedTableSource $ts)
    {
        if ($ts->tableSource instanceof Select)
        {
            return sprintf('(%s) %s', $this->tosql_select($ts->tableSource), $this->tosql_identifier($ts->alias));
        }
        else
        {
            return sprintf('%s %s', $this->tosql_tableSource($ts->tableSource), $this->tosql_identifier($ts->alias));
        }
    }

    public function tosql_dual()
    {
        return 'dual';
    }


    /**
     *
     * @param iExpression
     * @return type
     */
    protected function tosql_tableSource(Element\iTableSource $ts)
    {
        return $ts->toSQL($this);
    }

    /**
     *
     * @param Element\OrderRule[]
     * @return string
     * @throws WDB\Exception\BadArgument
     */
    protected function tosql_order($rules)
    {
        $first = TRUE;
        $q = '';
        foreach ($rules as $rule)
        {
            $first == TRUE ? $first = FALSE : $q .= ", "; //comma before every element except first one
            if ($rule instanceof Element\OrderRule)
            {
                $q .= $rule->expression->toSQL($this);
                if ($rule->descending) $q .= ' DESC';
            }
            else
            {
                throw new WDB\Exception\BadArgument("order rule list must be array of OrderRule");
            }
        }
        return $q;
    }

    /**
     * Convert array to update assign list string.
     *
     * @param WDB\Analyzer\Table
     * @param array
     * @return string
     */
    protected function tosql_assignList(WDB\Analyzer\Table $table = NULL, $items = array())
    {
        $first = TRUE;
        $q = '';
        foreach ($items as $column=>$value)
        {
            $first == TRUE ? $first = FALSE : $q .= ", "; //comma before every element except first one
            if ($value instanceof Element\iExpression)
            {
                $val = $value->toSQL($this);
            }
            else
            {
                if ($value === NULL)
                {
                    $val = 'NULL';
                }
                else
                {
                    if ($table !== NULL)
                    {
                        if (!isset($table->columns[$column])) throw new \WDB\Exception\NotFound("column $column not found in {$table->name}");
                        $val = $table->columns[$column]->valueToDatatype($value)->toSQL($this);
                    }
                    else
                    {
                        $val = WDB\Query\Element\Datatype\AbstractType::createDatatype($value)->toSQL($this);
                    }
                }
            }
            $q .= $this->tosql_identifier($column).'='.$val;
        }
        return $q;
    }

    protected function tosql_selectFieldList($list)
    {
        $first = TRUE;
        $q = '';
        foreach ($list as $field)
        {
            $first == TRUE ? $first = FALSE : $q .= ", "; //comma before every element except first one
            if ($field instanceof Element\SelectField)
            {
                $q .= $field->expression->toSQL($this);
                if ($field->alias) $q .= " AS ".$this->tosql_identifier($field->alias);
            }
            elseif ($field instanceof Element\iExpression)
            {
                $q .= $field->toSQL($this);
            }
            else
            {
                throw new WDB\Exception\BadArgument("field list must be array of iExpression or SelectField");
            }
        }
        return $q;
    }

    protected function tosql_select(Select $select)
    {
        if (count($select->getFields()) == 0)
        {
            throw new Exception\InvalidOperation("Select query must have at least one field to select, none given");
        }
        $q = "SELECT ";
        if ($select->getDistinct())
        {
            $q .= "DISTINCT ";
        }
        $q .= $this->tosql_selectFieldList($select->fields);

        if ($select->table)
        {
            $q .= "\nFROM ".$this->tosql_tableSource($select->table);
        }
        if ($select->where)
        {
            $q .= "\nWHERE ".$select->where->toSQL($this);
        }
        if ($select->group)
        {
            $q .= "\nGROUP BY ".$this->tosql_selectFieldList($select->group);
        }
        if ($select->having)
        {
            $q .= "\nHAVING ".$select->having->toSQL($this);
        }
        if ($select->order)
        {
            $q .= "\nORDER BY ".$this->tosql_order($select->order);
        }
        if ($select->limit || $select->offset)
        {
            $limit = $select->limit ? $select->limit : '18446744073709551615'; //has no limit but nonzero offset, so provide maximum mysql number as limit
            $q .= "\nLIMIT ".$limit;
            if ($select->offset) $q .= ' OFFSET '.$select->offset;
        }
        if ($select->union !== NULL)
        {
            $q .= "\n UNION";
            if ($select->unionAll)
            {
                $q .= ' ALL';
            }
            $q .= "\n".$select->union->toSQL($this);
        }
        return $q;
    }

    protected function tosql_delete(\WDB\Query\Delete $delete)
    {
        $q = 'DELETE FROM ';
        $q .= $delete->table->toSQL($this);
        if ($delete->where)
        {
            $q .= "\nWHERE ".$delete->where->toSQL($this);
        }
        if ($delete->order)
        {
            $q .= "\nORDER BY ".$this->tosql_order($delete->order);
        }
        if ($delete->limit)
        {
            $q .= "\nLIMIT ".$delete->limit;
        }
        if ($delete->offset)
        {
            throw new Exception\UnsupportedByDriver("Delete command does not support offsets.");
        }
        return $q;
    }

    protected function tosql_update(\WDB\Query\Update $update)
    {
        $q = 'UPDATE ';
        $q .= $update->table->toSQL($this);
        $q .= ' SET ';
        $q .= $this->tosql_assignList($update->getDatabase() ? $update->getDatabase()->getTable($update->table) : NULL, $update->columns);
        if ($update->where)
        {
            $q .= "\nWHERE ".$update->where->toSQL($this);
        }
        if ($update->order)
        {
            $q .= "\nORDER BY ".$this->tosql_order($update->order);
        }
        if ($update->limit)
        {
            $q .= "\nLIMIT ".$update->limit;
        }
        return $q;
    }

    protected $integer_ranges = array(
        'tinyint'=>array('-128', '127'),
        'smallint'=>array('-32768', '32767'),
        'mediumint'=>array('-8388608', '8388607'),
        'int'=>array('-2147483648', '2147483647'),
        'bigint'=>array('-9223372036854775808', '9223372036854775807'),
        );

    /**
     * Parses SQL string representing ENUM/SET identifiers into an array
     *
     * @param string $set
     * @return array
     */
    protected static function parseSet($set)
    {
        for ($start = 1; $start <= strlen($set) && $set{$start-1} != "'"; ++$start) {}
        $dictionary = array();
        while ($start < strlen($set))
        {
            $end = $start;
            do
            {
                if ($set{$end} == "'")
                {
                    if ($end < strlen($set)-1 && $set{$end+1} == "'")
                    {
                        $end += 2;
                    }
                    else
                    {
                        break;
                    }
                }
                else
                {
                    ++$end;
                }
            } while ($end < strlen($set));
            $dictionary[] = str_replace("''", "'", substr($set, $start, $end-$start));
            for ($start = $end+2; $start <= strlen($set) && $set{$start-1} != "'"; ++$start) {}
        }
        return $dictionary;
    }

    protected function fkeyAction($keyword)
    {
        switch ($keyword)
        {
            case 'CASCADE':
                return WDB\Structure\ForeignKeyData::CASCADE;
            case 'SET NULL':
                return WDB\Structure\ForeignKeyData::SETNULL;
            default:
                return WDB\Structure\ForeignKeyData::RESTRICT;
        }
    }

    public function commit()
    {
        $this->queryRaw("COMMIT");;
    }

    public function rollback()
    {
        $this->queryRaw("ROLLBACK");
    }
}